package com.brother.database;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.log4j.Logger;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class TanzaniaAddressDB {
	public static Properties props = null;

	private static Logger log = Logger.getLogger(TanzaniaAddressDB.class);

	private static ComboPooledDataSource dataSource = null;
	static{
		InputStream in = DataBase.class.getResourceAsStream("/jdbc.properties");
		props = new Properties();
		try {
			props.load(in);
		} catch (IOException e) {
			
		}finally {
			if(in != null)
				try {
					in.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
		}
		
		String url = props.getProperty("gis.tanzania.jdbc.url");
		String username = props.getProperty("jdbc.username");
		String password = props.getProperty("jdbc.password");
		String driver = props.getProperty("jdbc.driver");
		
		dataSource = new ComboPooledDataSource(true); 
		 try {
			 dataSource.setDriverClass(driver);
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		} 
		 //loads the jdbc driver 
		dataSource.setJdbcUrl(url);
		dataSource.setUser(username); 
		dataSource.setPassword(password); 
		dataSource.setMaxPoolSize(50);
		dataSource.setMinPoolSize(20);
		dataSource.setMaxIdleTime(0);// second
		dataSource.setIdleConnectionTestPeriod(600);// second
		dataSource.setTestConnectionOnCheckin(true);
		dataSource.setAcquireRetryDelay(1000);// millisecond
		dataSource.setMaxStatements(0);
		dataSource.setMaxStatementsPerConnection(0);
//		dataSource.setUnreturnedConnectionTimeout(300);// second
		dataSource.setAutomaticTestTable("testtable");
		dataSource.setBreakAfterAcquireFailure(false);
	}

	private static final ThreadLocal<Connection> tl = new ThreadLocal<Connection>();

	public static Connection getConnection() {
		Connection conn = null;
		
		try {
			
			conn = tl.get();
			if(conn == null || conn.isClosed()){
				if(log.isDebugEnabled()){
					log.debug("Getting the thread connection...");
				}
				for(int i=0; i < 20;i++){
					conn = dataSource.getConnection();
					if(!conn.isClosed()){
						tl.set(conn);
						break;
					}else{
						dataSource.resetPoolManager(true);
					}
				}
			}
		} catch (SQLException e) {
			Logger.getLogger(DataBase.class).error("",e);
		}
		
		return conn;
	}

	public static void closeConnection(Connection conn) {
		try {
			if(conn != null && !conn.isClosed()) {
				conn.close();
			}
		} catch (SQLException e) {
			Logger.getLogger(DataBase.class).error(e);
		}
	}
		
	public static int update(String sql) {
		Connection conn = getConnection();
		if(conn != null) {
			try {
				Statement stmt = conn.createStatement();
				return stmt.executeUpdate(sql);
			} catch (SQLException e) {
				Logger.getLogger(DataBase.class).error(sql,e);
			}finally{
				closeConnection(conn);
			}
		}
		return 0;
	}

	public static ResultSet query(String sql) {
		ResultSet rs = null;
		Connection conn = getConnection();
		if(conn != null) {
			try {
				Statement stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
			} catch (SQLException e) {
				Logger.getLogger(DataBase.class).error(sql,e);	
			}
		}	
		return rs;
	}

	public static void closeConnection() {
		//  Now it is replaced with the closeThreadConnection
	}

	public static void closeThreadConnection(){
		try {
			
			
			if(tl.get() != null && !tl.get().isClosed()) {
				if(log.isDebugEnabled()){
					log.debug("Closing the thread connection...");
				}
				tl.get().close();
			}
		} catch (SQLException e) {
			log.error("",e);
		}
	}
}
